package dao;

import dto.MedicoDTO;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author lucifer
 */
public class MedicoDAO {

  public static final String Tab =
    "create table medico (" +
    "id serial," + //pk
    "nombre text," +
    "apellido text," +
    "dni int," +
    "matricula text," +
    "id_especialidad int," + //fk
    "tel_linea text," +     
    "celular text" +
    ");" +

    "alter table medico add constraint medico_fk foreign key (id_especialidad) references especialidad(id);"
  ;

  public static final String Tuples = "";
    /*
    "insert into medico (nombre,apellido,dni,matricula,id_especialidad,tel_linea,celular) values" +
    "('Leonard', 'McCoy', 78974379, 'M514719', 1, '30462-1160', '15-5889-8812')," +
    "('Ernesto', 'Guevara', 25610126, 'M325695', 1, '4814-8812', '15-6811-7948')," +
    "('Ramón', 'Carrillo', 23168297, 'M253625', 3, '5257-9394', '15-3780-9225')," +
    "('Otto', 'Octavius', 9915593, 'M773169', 1, '9261-2417', '15-4114-8927')," +
    "('René', 'Favaloro', 37187055, 'M261854', 2, '5733-6865', '15-9625-2780')," +
    "('Nick', 'Riviera', 31897948, 'M754862', 2, '5506-8927', '15-6410-3362')," +
    "('Ignacio', 'Pirovano', 34115045, 'M910113', 3, '6327-7229', '15-4215-3086')," +
    "('Julius', 'Hibbert', 11806005, 'M809444', 4,  '4863-3854', '15-5197-8565')," +
    "('Alicia', 'Moreau de Justo', 15149719, 'M597379', 1, '05968-2881', '15-6470-3756')," +
    "('Juan Pedro', 'Garrahan', 2650245, 'M158655', 4, '8386-6679', '15-5291-9630')," +
    "('Juan Luis', 'Manzur', 35106066, 'M351606', 1, '9699-6307', '15-9837-6307')," +
    "('Ginés', 'González García', 29158655, 'M250245', 5, '6375-8397', '15-4819-3614');"
  ;
  */

  private static MedicoDAO dao = null;
  private final Connection c;

  public static MedicoDAO createDAO() throws SQLException {
    if (dao == null) {
      dao = new MedicoDAO();
    }
    return dao;
  }

  private MedicoDAO() throws SQLException {
    c = DB.PQ().Conn();
  }

  public int LastID() throws SQLException {
    return DB.PQ().LastIDFrom("medico");
  }

  public ArrayList<MedicoDTO> All() throws SQLException {
    return getMedicoDTOs("select * from medico order by apellido, nombre, id;");
  }

  public ArrayList<MedicoDTO> SearchByNombreApellido(String s) throws SQLException {
    return getMedicoDTOs(queryByNombreApellido(s));
  }

  // hace falta poder buscar a los médicos por dni?
  public ArrayList<MedicoDTO> SearchByDNI(int dni) throws SQLException {
    throw new UnsupportedOperationException("?not supported yet");
  }

  private String queryByNombreApellido(String name) throws SQLException {
    String query = String.format(
      "select * from medico where nombre ~* '^%s' or apellido ~* '^%s' order by apellido, nombre, id;",
      name, name
    );
    return query;
  }

  private ArrayList<MedicoDTO> getMedicoDTOs(String query) throws SQLException {
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(query);
    ArrayList<MedicoDTO> medicos = new ArrayList<>();
    while (r.next()) {
      medicos.add(
        new MedicoDTO(
          r.getInt("id"),
          r.getString("nombre"),
          r.getString("apellido"),
          r.getInt("dni"),
          r.getString("matricula"),
          r.getInt("id_especialidad"),
          r.getString("tel_linea"),
          r.getString("celular")
        )
      );
    }
    r.close();
    s.close();
    return medicos;
  }

  public MedicoDTO MedicoDTO(int id) throws SQLException {
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(
      "select * from medico where id = " + id
    );
    MedicoDTO dto = null;
    if (r.next()) {
      dto = new MedicoDTO(
        r.getInt("id"),
        r.getString("nombre"),
        r.getString("apellido"),
        r.getInt("dni"),
        r.getString("matricula"),
        r.getInt("id_especialidad"),
        r.getString("tel_linea"),
        r.getString("celular")
      );
    }
    r.close();
    s.close();
    return dto; //si no existe devuelve null
  }

  // Guarda un **nuevo** médico.
  public int Save(MedicoDTO m) throws SQLException {
    String query = String.format(
      "insert into medico values (default, '%s', '%s', %d, '%s', %d, '%s', '%s');",
      m.getNombre(), m.getApellido(), m.getDni(), m.getMatricula(), m.getIdEspecialidad(), m.getTelLinea(), m.getCelular()
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
    return LastID();
  }

  public void Update(MedicoDTO m) throws SQLException {
    String query = String.format(
      "update medico set nombre = '%s', apellido = '%s', dni = %d, matricula = '%s', id_especialidad = %d, tel_linea = '%s', celular = '%s' where id = %d;",
      m.getNombre(), m.getApellido(), m.getDni(), m.getMatricula(), m.getIdEspecialidad(), m.getTelLinea(), m.getCelular(), m.getId()
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }

}
